from datetime import datetime
from typing import Union, Optional

from sqlalchemy import update, CursorResult, text, asc, func, insert, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import select, desc, col
from sqlmodel.sql.expression import SelectOfScalar, Select, and_

from src.base.beans import PageResult
from src.base.constants import id_generator
from src.domain.sys_post import SysPost, SysPostReq


async def get_post_by_id(dbSession: AsyncSession, id: int) -> SysPost:
    result = await dbSession.execute(select(SysPost).where(SysPost.post_id == id).where(SysPost.del_flag == False))
    return result.scalar_one_or_none()


async def get_post_list_by_ids(dbSession: AsyncSession, ids: list[int]) -> list[SysPost]:
    result = await dbSession.execute(select(SysPost).where(col(SysPost.post_id).in_(ids)))
    return list(result.scalars().all())


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysPostReq):
    stmt = stmt.where(SysPost.del_flag == False)
    if entity:
        if entity.post_id:
            stmt = stmt.where(SysPost.post_id == entity.post_id)
        if entity.post_name:
            stmt = stmt.where(col(SysPost.post_name).like(f"%{entity.post_name}%"))
        if entity.post_code:
            stmt = stmt.where(col(SysPost.post_code).like(f"%{entity.post_code}%"))
        if entity.dept_id :
            stmt = stmt.where(SysPost.dept_id == entity.dept_id)
        if entity.status is not None:
            stmt = stmt.where(SysPost.status == entity.status)
        if entity.create_time_range:
            start_time = datetime.strptime(entity.create_time_range[0], "%Y-%m-%d").date()
            end_time = datetime.strptime(entity.create_time_range[1], "%Y-%m-%d").date()
            stmt = stmt.where(
                and_(func.date(SysPost.create_time) >= start_time, func.date(SysPost.create_time) <= end_time))
    return stmt


async def get_post_list_by_condition(dbSession: AsyncSession, entity: Optional[SysPostReq] = None) -> list[SysPost]:
    if not entity:
        entity = SysPost()
    stmt = select(SysPost)
    stmt = where_handle(stmt, entity)
    stmt = stmt.order_by(desc(SysPost.sort))
    result = await dbSession.execute(stmt)
    return list(result.scalars())


async def get_post_page_by_condition(dbSession: AsyncSession, entity: Optional[SysPostReq] = None) -> PageResult:
    if entity is None:
        entity = SysPostReq()
    list_stmt = select(SysPost)
    list_stmt = where_handle(list_stmt, entity)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysPost.post_name))
    list_stmt = list_stmt.limit(entity.pageSize).offset(entity.pageSize * (entity.current - 1))
    data = await dbSession.execute(list_stmt)
    data = list(data.scalars())

    total_stmt = select(func.count(SysPost.post_id))
    total_stmt = where_handle(total_stmt, entity)
    total_result = await dbSession.execute(total_stmt)
    total = total_result.scalar_one_or_none()
    return PageResult(total=total, data=data, current=entity.current, pageSize=entity.pageSize)


async def insert_post(dbSession: AsyncSession, entity: SysPostReq):
    entity.post_id = next(id_generator)
    insert_data = SysPost(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(insert(SysPost).values(**insert_data))
    # entity.post_id = result.lastrowid
    return int(result.rowcount)


async def update_post(dbSession: AsyncSession, entity: SysPostReq) -> int:
    update_data = SysPost(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    stmt = update(SysPost).values(**update_data).where(col(SysPost.post_id).__eq__(entity.post_id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_post(dbSession: AsyncSession, id: int = None) -> int:
    # stmt = update(SysPost).values({"del_flag": True}).where(col(SysPost.post_id).__eq__(id))
    stmt = delete(SysPost).where(col(SysPost.post_id).__eq__(id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_posts(dbSession: AsyncSession, ids: list[int] = None) -> int:
    # stmt = update(SysPost).values({"del_flag": True}).where(col(SysPost.post_id).in_(ids))
    stmt = delete(SysPost).where(col(SysPost.post_id).in_(ids))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)
